package com.to8to.autotask.allocate.zhkf;

import static com.to8to.autotask.util.AutoTaskType.ALLOCATE_ZHKF;
import static com.to8to.autotask.util.DBOperaType.UPDATE;
import static com.to8to.es.db.dao.DBTables.TABLE_ZB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;

import org.elasticsearch.common.collect.Lists;
import org.elasticsearch.common.collect.Maps;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.to8to.autotask.rpc.SendMsg2ES;
import com.to8to.autotask.util.DBUtil;
import com.to8to.autotask.util.TwoTuple;
import com.to8to.autotask.util.Utils;
import com.to8to.autotask.zbinfo.ZBInfo;
import com.to8to.es.util.LogUtil;

/**
 * AllocateZHKFAlgorithm.java
 * 作 者：atom.liu
 * 版 本：v2.0
 * 日 期：2014年11月29日
 * 描 述：分配装后客服算法。包访问权限。
 */
final class AllocateZHKFAlgorithm
{
    private static final Logger logger = LoggerFactory.getLogger(AllocateZHKFTask.class);

    /**
     * SQL:查询出符合条件的中标记录。关联to8to_yuyue_apply进行查询：要使用to8to_yuyue_apply表中的cityid
     * chuli=2：审核通过
     * zxbyh<2：装修保+装后保用户
     * zhkf_id=0:没有装后客服
     * iscostbzj != 2:保障金状态不是已退
     * 
     * ftime > 今天 and jg_time = 0：项目还没有结束  2015-01-15这个条件先去掉
     */
    private static final String ZB_RECORD_SQL =
        "select * from to8to_jj_smt_zb z left join to8to_yuyue_apply a on z.yid = a.yid where z.chuli = 2 and z.zxbyh < 2 and z.zhkf_id = 0 and z.iscostbzj != 2 and a.cityid != 0 and a.cityid is not null";

    /**
     * SQL:查询出装后客服。
     * isclose = 0：在职
     * cityids != '' and cityids ： 有负责的城市
     */
    private static final String CUST_SERVICER_SQL =
        "select * from to8to_user_info where crmroleid = 17 and isclose = 0 and cityids != '' and cityids is not null ";

    /**
     * SQL:分配操作。就是update中标记录的zkkf_id字段。
     */
    private static final String ALLOCATE_SQL = "update to8to_jj_smt_zb set zhkf_id=%s where zid=%s and zhkf_id=0";

    /**
     * 排序策略
     */
    private static final StrategyWithZBCount STRATEGY = new StrategyWithZBCount();

    public static void allocate()
    {
        Connection connect = null;

        try
        {
            connect = DBUtil.getConnection(false);

            //获取需要分配装后客服的中标记录,并进行分组。key：cityid value：中标信息集合
            Map<Integer, List<ZBInfo>> cityZBMap = getCityZBMap(connect);
            if (Utils.isNullOrEmpty(cityZBMap))
            {
                logger.info("本次符合条件的中标记录个数为0");
                return;
            }

            //获取可分配的装后客服，按照城市id进行分组
            Map<Integer, List<CustServicer>> cityCustMap = getCityCustMap(connect);
            if (Utils.isNullOrEmpty(cityCustMap))
            {
                logger.info("查询出装后客服的个数为0");
                return;
            }

            //执行分配
            TwoTuple<List<String>, List<String>> tuple = allocate(cityZBMap, cityCustMap);

            //将分配的操作刷新到数据库
            DBUtil.refresh2DB(connect, tuple.first, ALLOCATE_ZHKF, new SendMsg2ES(UPDATE, TABLE_ZB, tuple.second));
        }
        catch (Throwable e)
        {
            logger.error("分配装后客服失败！{} \n {}", e, LogUtil.logStackTrace(e));
            DBUtil.rollback(connect);
        }
        finally
        {
            DBUtil.releaseSource(null, null, connect);
        }
    }

    /**
     * 获取需要分配装后客服的中标记录，按照cityid进行分组。
     */
    private static Map<Integer, List<ZBInfo>> getCityZBMap(Connection connect) throws SQLException
    {
        PreparedStatement stat = connect.prepareStatement(ZB_RECORD_SQL);
        ResultSet result = stat.executeQuery();

        List<ZBInfo> zbinfos = Lists.newArrayList();
        while (result.next())
        {
            ZBInfo zbinfo = new ZBInfo();
            zbinfo.setUid(result.getInt("z.uid"));
            zbinfo.setYid(result.getInt("z.yid"));
            zbinfo.setZid(result.getInt("z.zid"));
            zbinfo.setCityid(result.getInt("a.cityid"));

            zbinfos.add(zbinfo);
        }
        DBUtil.releaseSource(result, stat, null);

        Map<Integer, List<ZBInfo>> map = Maps.newHashMap();
        for (ZBInfo zbinfo : zbinfos)
        {
            int cityId = zbinfo.getCityid();
            List<ZBInfo> list = map.get(cityId);
            if (list == null)
            {
                list = new ArrayList<ZBInfo>();
                map.put(cityId, list);
            }
            list.add(zbinfo);
        }
        return map;
    }

    /**
     * 查询出装后客服，并根据城市id进行分组。
     */
    private static Map<Integer, List<CustServicer>> getCityCustMap(Connection connect) throws SQLException
    {
        PreparedStatement stat = connect.prepareStatement(CUST_SERVICER_SQL);
        ResultSet result = stat.executeQuery();
        List<CustServicer> custList = new ArrayList<CustServicer>();

        while (result.next())
        {
            CustServicer cs = new CustServicer();
            cs.setUid(result.getInt("uid"));
            cs.setRoleid(result.getInt("crmroleid"));
            cs.setUsername(result.getString("username"));
            String citystr = result.getString("cityids");
            cs.setCityids(Utils.parseIntegerList(citystr, ","));

            custList.add(cs);
        }
        DBUtil.releaseSource(result, stat, null);

        Map<Integer, List<CustServicer>> map = Maps.newHashMap();
        for (CustServicer servicer : custList)
        {
            for (Integer cityid : servicer.getCityids())
            {
                List<CustServicer> list = map.get(cityid);
                if (list == null)
                {
                    list = new ArrayList<CustServicer>();
                    map.put(cityid, list);
                }
                list.add(servicer);
            }
        }

        return map;
    }

    /**
     * 执行分配 
     * @return TwoTuple二元组。first：要更新的sql语句集合 ; sceond：要更新的zid集合。
     */
    private static TwoTuple<List<String>, List<String>> allocate(Map<Integer, List<ZBInfo>> cityZBMap, Map<Integer, List<CustServicer>> cityCustMap)
    {
        List<String> sqlList = Lists.newArrayList();
        List<String> updateZidList = Lists.newArrayList();
        TwoTuple<List<String>, List<String>> tuple = TwoTuple.newInstance(sqlList, updateZidList);

        for (Integer cityid : cityZBMap.keySet())
        {
            List<CustServicer> custList = cityCustMap.get(cityid);
            if (custList == null || custList.size() == 0)
            {
                logger.info(String.format("该城市[ %s ]没有负责的装后客服 ", cityid));
                continue;
            }

            List<ZBInfo> zbinfos = cityZBMap.get(cityid);
            for (int i = 0; i < zbinfos.size(); i++)
            {
                //在每次分配前，重新排序
                Collections.sort(custList, STRATEGY);

                ZBInfo info = zbinfos.get(i);
                CustServicer cs = custList.get(0);
                sqlList.add(String.format(ALLOCATE_SQL, cs.getUid(), info.getZid()));
                updateZidList.add(String.valueOf(info.getZid()));
                logger.info(String.format("中标记录[ %s ] 分配到  装后客服  [ %s  %s]", info.getZid(), cs.getUid(), cs.getUsername()));
                cs.setAllocatedCount(cs.getAllocatedCount() + 1);
            }
        }

        return tuple;
    }

    /**
     * 静态内部类。根据每个客服的当天所分配的数量来进行排序。 
     */
    private static class StrategyWithZBCount implements Comparator<CustServicer>
    {
        @Override
        public int compare(CustServicer o1, CustServicer o2)
        {
            int result = o1.getAllocatedCount() - o2.getAllocatedCount();
            if (result == 0) //如果本次分配只有一个项目且多个负责装后客服，则随机选取一个装后客服，防止每次分配个数为1的时候，都分配给了同一个客服
                result = Utils.getRandomBoolean() ? -1 : 1;
            return result;
        }
    }
}
